/*
 *this file is part of nseer erp
 *Copyright (C)2006-2010 Nseer(Beijing) Technology co.LTD/http://www.nseer.com 
 *
 *This program is free software; you can redistribute it and/or
 *modify it under the terms of the GNU General Public License
 *as published by the Free Software Foundation; either
 *version 2 of the License, or (at your option) any later version.
 */
package include.excel_import;

import java.io.InputStream;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;

public class XlsValidator extends Path {

    private String fileName = null;
    private HSSFWorkbook wb = null;
    private StringBuffer message = new StringBuffer("");
    private XlsInfo xlsInfo = null;

    public void setFile(String fileName) throws Exception {
        this.fileName = getPath() + "/conf/excel_import/" + fileName;
        if (fileName == null) {
            throw new Exception("Exception: 必须指定验证的Excel文件名");
        }
        try {
            InputStream input = new FileInputStream(this.fileName);
            POIFSFileSystem fs = new POIFSFileSystem(input);
            wb = new HSSFWorkbook(fs);
            xlsInfo = new XlsInfo(wb);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean validate() {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            if (validateSheet(sheet)) {
                String sheetName = wb.getSheetName(i);
                if (validateTitle(sheet, sheetName)) {
                    if (validateContent()) {
                        if (validateValue()) {
                            return true;
                        }
                    }
                }
                sheetName = null;
            }
            sheet = null;
        }
        return false;
    }

    private boolean validateSheet(HSSFSheet sheet) {
        if (sheet == null) {
            message.append("有空的sheet");
            return false;
        } else if (sheet.getNumMergedRegions() > 0) {
            message.append("有列合并的情况<br>");
            return false;
        }
        return true;
    }

    private boolean validateTitle(HSSFSheet sheet,
            String sheetName) {
        HSSFRow row = sheet.getRow((short) 0);
        if (row == null) {
            message.append("有空标题的情况,或者空的SHEET");
            return false;
        }
        Iterator cells = row.cellIterator();
        int size = 0;
        while (cells.hasNext()) {
            HSSFCell cell = (HSSFCell) cells.next();
            size++;
        }
        for (int j = 0; j < size - 1; j++) {
            HSSFCell cell = row.getCell((short) j);
            if (cell == null) {
                return false;
            } else {
                if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
                    message.append(""/*sheetName*/).append("的第");
                    message.append(j + 1).append("列的标题类型不对<br>");
                    return false;
                }
                if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                    message.append(""/*sheetName*/).
                            append("的第").append(j + 1).append("列的标题为空<br>");
                    return false;
                }
            }
        }
        return true;
    }

    public boolean validateType(HSSFCell cell,
            String columnName,
            String sheetName) {
        try {
            String itemType =
                    xlsInfo.getColumnType(columnName, sheetName);
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    if (itemType.equals("INT") ||
                            itemType.equals("DOUBLE") ||
                            itemType.equals("DATE")) {
                        return false;
                    }
                    break;
                default:
                    break;
            }
            itemType = null;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean validateContent() {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            String sheetName = wb.getSheetName(i);
            int rowCount = xlsInfo.getRowCount(sheetName);
            for (int j = 1; j < rowCount; j++) {
                HSSFRow row = sheet.getRow(j);
                for (int n = 0; n < xlsInfo.getColumnCount(sheetName); n++) {
                    HSSFCell cell = row.getCell((short) n);
                    if (cell == null) {
                        message.append(""/*sheetName*/).append("有的行或者列(列");
                        message.append(n + 1).append(":行");
                        message.append(j + 1).append(")为空(");
                        message.append(""/*sheetName*/).append("记录总行数是");
                        message.append(rowCount).append("吗?)<br>");
                        return false;
                    }
                }
            }
        }
        return true;
    }

    public boolean validateValue() {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            String sheetName = wb.getSheetName(i);
            int rowCount = xlsInfo.getRowCount(sheetName);
            for (int j = 1; j < rowCount; j++) {
                HSSFRow row = sheet.getRow(j);
                HSSFRow row1 = sheet.getRow(0);
                for (int n = 0; n < xlsInfo.getColumnCount(sheetName); n++) {
                    HSSFCell cell1 = row1.getCell((short) n);
                    String itemname = cell1.getStringCellValue();
                    HSSFCell cell = row.getCell((short) n);
                    if (!validateType(cell, itemname, sheetName)) {
                        message.append("第").append(n + 1);
                        message.append("列 : 第").append(j + 1);
                        message.append("行变量类型不符合<br>");
                        return false;
                    }
                    cell1 = null;
                    itemname = null;
                    cell = null;
                }
                row = null;
                row1 = null;
            }
            sheet = null;
            sheetName = null;
        }
        return true;
    }

    public String getMessage() {
        return message.toString();
    }
}
